using System;
using System.Collections.Generic;
using System.Text;
using Emlyn.Common.Data.SqlServer.DataLayer;
using System.Data;
using System.Data.SqlClient;
using Emlyn.Common.Data.SqlServer.SqlDBAccess;

namespace Emlyn.Website.Articles
{
    public class xArticleListDataLayer : SimpleTableDataLayer<xArticleList, Guid>
    {
        public DataSet GetArticleListByArticleListCode(string aArticleListCode)
        {
            string lSql = "select * from tblEOArticleList where ArticleListCode=@ArticleListCode";
            SqlParameter lArticleListCodeParameter = new SqlParameter("@ArticleListCode", aArticleListCode);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleListCodeParameter);

            return SimpleSQL.GetDataSet(lSql, lParameters);
        }

        public DataSet GetChildArticleListsByArticleListID(Guid aArticleListID)
        {
            string lSql =
                "select al.* " +
                "from tblEOArticleList al " +
                "join tblEOArticleList_ArticleList aal " +
                "on al.ArticleListID = aal.ChildArticleListID " +
                "where aal.ArticleListID=@ArticleListID " +
                "order by al.ArticleListName ";
            SqlParameter lArticleListIDParameter = new SqlParameter("@ArticleListID", aArticleListID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleListIDParameter);

            return SimpleSQL.GetDataSet(lSql, lParameters);
        }

        public DataSet GetNonChildArticleListsByArticleListID(Guid aArticleListID)
        {
            string lSql =
                "select al.* " +
                "from tblEOArticleList al " +
                "left join (" +
                "   select * " +
                "   from tblEOArticleList_ArticleList " +
                "   where ArticleListID=@ArticleListID " +
                ") aal " +
                "on al.ArticleListID = aal.ChildArticleListID " +
                "where aal.ChildArticleListID is null " +
                "and al.ArticleListID <> @ArticleListID " +
                "order by al.ArticleListName ";
            SqlParameter lArticleListIDParameter = new SqlParameter("@ArticleListID", aArticleListID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleListIDParameter);

            return SimpleSQL.GetDataSet(lSql, lParameters);
        }

        public DataSet GetTopLevelArticleLists()
        {
            string lSql =
                "select al.* " +
                "from tblEOArticleList al " +
                "left join tblEOArticleList_ArticleList aal " +
                "on al.ArticleListID = aal.ChildArticleListID " +
                "where aal.ArticleListID is null " +
                "order by al.ArticleListName ";

            return SimpleSQL.GetDataSet(lSql);
        }

        public DataSet GetParentArticleListsByArticleListID(Guid aArticleListID)
        {
            string lSql =
                "select al.* " +
                "from tblEOArticleList al " +
                "join tblEOArticleList_ArticleList aal " +
                "on al.ArticleListID = aal.ArticleListID " +
                "where aal.ChildArticleListID=@ArticleListID " +
                "order by al.ArticleListName ";
            SqlParameter lArticleListIDParameter = new SqlParameter("@ArticleListID", aArticleListID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleListIDParameter);

            return SimpleSQL.GetDataSet(lSql, lParameters);
        }

        public DataSet GetNonParentArticleListsByArticleListID(Guid aArticleListID)
        {
            string lSql =
                "select al.* " +
                "from tblEOArticleList al " +
                "left join (" +
                "   select * " +
                "   from tblEOArticleList_ArticleList " +
                "   where ChildArticleListID=@ArticleListID " +
                ") aal " +
                "on al.ArticleListID = aal.ArticleListID " +
                "where aal.ArticleListID is null " +
                "and al.ArticleListID <> @ArticleListID " +
                "order by al.ArticleListName ";
            SqlParameter lArticleListIDParameter = new SqlParameter("@ArticleListID", aArticleListID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleListIDParameter);

            return SimpleSQL.GetDataSet(lSql, lParameters);
        }

        protected override string TableName()
        {
            return "tblEOArticleList";
        }

        protected override string KeyName()
        {
            return "ArticleListID";
        }

        protected override IList<string> NonKeyAttributeNames()
        {
            List<string> retval = new List<string>();

            retval.Add("ArticleListCode");
            retval.Add("ArticleListName");
            retval.Add("ArticleListDescription");

            return retval;
        }

        protected override Guid DoGetKey(xArticleList aInstance)
        {
            return aInstance.ArticleListID;
        }

        protected override object GetParameterValue(xArticleList aInstance, string aParameterName)
        {
            switch (aParameterName)
            {
                case "ArticleListCode": return aInstance.ArticleListCode;
                case "ArticleListName": return aInstance.ArticleListName;
                case "ArticleListDescription": return aInstance.ArticleListDescription;
            }

            throw new ApplicationException("Unknown Parameter Requested: " + aParameterName);
        }

        public void AddChildArticleList(Guid aArticleListID, Guid aChildArticleListID)
        {
            string lSql =
                "insert tblEOArticleList_ArticleList " +
                "(ArticleList_ArticleListID, ArticleListID, ChildArticleListID) " +
                "values " +
                "(@ArticleList_ArticleListID, @ArticleListID, @ChildArticleListID) ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();
            
            lParameter = new SqlParameter("@ArticleList_ArticleListID", Guid.NewGuid());
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ChildArticleListID", aChildArticleListID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }

        public void RemoveChildArticleList(Guid aArticleListID, Guid aChildArticleListID)
        {
            string lSql =
                "delete tblEOArticleList_ArticleList " +
                "where ArticleListID = @ArticleListID " + 
                "and ChildArticleListID = @ChildArticleListID ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ChildArticleListID", aChildArticleListID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }

        public void AddParentArticleList(Guid aArticleListID, Guid aParentArticleListID)
        {
            string lSql =
                "insert tblEOArticleList_ArticleList " +
                "(ArticleList_ArticleListID, ArticleListID, ChildArticleListID) " +
                "values " +
                "(@ArticleList_ArticleListID, @ParentArticleListID, @ArticleListID) ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ArticleList_ArticleListID", Guid.NewGuid());
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ParentArticleListID", aParentArticleListID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }

        public void RemoveParentArticleList(Guid aArticleListID, Guid aParentArticleListID)
        {
            string lSql =
                "delete tblEOArticleList_ArticleList " +
                "where ArticleListID = @ParentArticleListID " +
                "and ChildArticleListID = @ArticleListID ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ParentArticleListID", aParentArticleListID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }

        public void AddArticleToArticleList(Guid aArticleListID, Guid aArticleID)
        {
            string lSql =
                "insert tblEOArticle_ArticleList " +
                "(Article_ArticleListID, ArticleListID, ArticleID) " +
                "values " +
                "(@Article_ArticleListID, @ArticleListID, @ArticleID) ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@Article_ArticleListID", Guid.NewGuid());
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ArticleID", aArticleID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }

        public void RemoveArticleFromArticleList(Guid aArticleListID, Guid aArticleID)
        {
            string lSql =
                "delete tblEOArticle_ArticleList " +
                "where ArticleListID = @ArticleListID " +
                "and ArticleID = @ArticleID ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ArticleListID", aArticleListID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ArticleID", aArticleID);
            lParameters.Add(lParameter);

            SimpleSQL.Execute(lSql, lParameters);
        }
    }
}
